readxl package
List the sheets of an Excel file
Before you can start importing from Excel, you should find out which sheets are available in the workbook. You can use the excel_sheets() function for this.
You will find the Excel file urbanpop.xlsx in your working directory (type dir() to see it). This dataset contains urban population metrics for practically all countries in the world throughout time (Source: Gapminder). It contains three sheets for three different time periods. In each sheet, the first row contains the column names.
# Load the readxl package
library(readxl)
# Print the names of all worksheets
excel_sheets("../xDatasets/urbanpop.xlsx")## [1] "1960-1966" "1967-1974" "1975-2011"As you can see, the result of excel_sheets() is simply a character vector; you haven’t imported anything yet. That’s something for the read_excel() function.
Import an Excel sheet
Now that you know the names of the sheets in the Excel file you want to import, it is time to import those sheets into R. You can do this with the read_excel() function. Have a look at this recipe:
data <- read_excel("data.xlsx", sheet = "my_sheet")
This call simply imports the sheet with the name "my_sheet" from the "data.xlsx" file. You can also pass a number to the sheet argument; this will cause read_excel() to import the sheet with the given sheet number. sheet = 1 will import the first sheet, sheet = 2 will import the second sheet, and so on.
# Read the sheets, one by one
pop_1 <- read_excel("../xDatasets/urbanpop.xlsx", sheet = 1)
pop_2 <- read_excel("../xDatasets/urbanpop.xlsx", sheet = 2)
pop_3 <- read_excel("../xDatasets/urbanpop.xlsx", sheet = 3)
# Put pop_1, pop_2 and pop_3 in a list: pop_list
pop_list = list(pop_1, pop_2, pop_3)
# Display the structure of pop_list
str(pop_list)## List of 3
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 8 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1960 : num [1:209] 769308 494443 3293999 NA NA ...
## ..$ 1961 : num [1:209] 814923 511803 3515148 13660 8724 ...
## ..$ 1962 : num [1:209] 858522 529439 3739963 14166 9700 ...
## ..$ 1963 : num [1:209] 903914 547377 3973289 14759 10748 ...
## ..$ 1964 : num [1:209] 951226 565572 4220987 15396 11866 ...
## ..$ 1965 : num [1:209] 1000582 583983 4488176 16045 13053 ...
## ..$ 1966 : num [1:209] 1058743 602512 4649105 16693 14217 ...
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 9 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1967 : num [1:209] 1119067 621180 4826104 17349 15440 ...
## ..$ 1968 : num [1:209] 1182159 639964 5017299 17996 16727 ...
## ..$ 1969 : num [1:209] 1248901 658853 5219332 18619 18088 ...
## ..$ 1970 : num [1:209] 1319849 677839 5429743 19206 19529 ...
## ..$ 1971 : num [1:209] 1409001 698932 5619042 19752 20929 ...
## ..$ 1972 : num [1:209] 1502402 720207 5815734 20263 22406 ...
## ..$ 1973 : num [1:209] 1598835 741681 6020647 20742 23937 ...
## ..$ 1974 : num [1:209] 1696445 763385 6235114 21194 25482 ...
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 38 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1975 : num [1:209] 1793266 785350 6460138 21632 27019 ...
## ..$ 1976 : num [1:209] 1905033 807990 6774099 22047 28366 ...
## ..$ 1977 : num [1:209] 2021308 830959 7102902 22452 29677 ...
## ..$ 1978 : num [1:209] 2142248 854262 7447728 22899 31037 ...
## ..$ 1979 : num [1:209] 2268015 877898 7810073 23457 32572 ...
## ..$ 1980 : num [1:209] 2398775 901884 8190772 24177 34366 ...
## ..$ 1981 : num [1:209] 2493265 927224 8637724 25173 36356 ...
## ..$ 1982 : num [1:209] 2590846 952447 9105820 26342 38618 ...
## ..$ 1983 : num [1:209] 2691612 978476 9591900 27655 40983 ...
## ..$ 1984 : num [1:209] 2795656 1006613 10091289 29062 43207 ...
## ..$ 1985 : num [1:209] 2903078 1037541 10600112 30524 45119 ...
## ..$ 1986 : num [1:209] 3006983 1072365 11101757 32014 46254 ...
## ..$ 1987 : num [1:209] 3113957 1109954 11609104 33548 47019 ...
## ..$ 1988 : num [1:209] 3224082 1146633 12122941 35095 47669 ...
## ..$ 1989 : num [1:209] 3337444 1177286 12645263 36618 48577 ...
## ..$ 1990 : num [1:209] 3454129 1198293 13177079 38088 49982 ...
## ..$ 1991 : num [1:209] 3617842 1215445 13708813 39600 51972 ...
## ..$ 1992 : num [1:209] 3788685 1222544 14248297 41049 54469 ...
## ..$ 1993 : num [1:209] 3966956 1222812 14789176 42443 57079 ...
## ..$ 1994 : num [1:209] 4152960 1221364 15322651 43798 59243 ...
## ..$ 1995 : num [1:209] 4347018 1222234 15842442 45129 60598 ...
## ..$ 1996 : num [1:209] 4531285 1228760 16395553 46343 60927 ...
## ..$ 1997 : num [1:209] 4722603 1238090 16935451 47527 60462 ...
## ..$ 1998 : num [1:209] 4921227 1250366 17469200 48705 59685 ...
## ..$ 1999 : num [1:209] 5127421 1265195 18007937 49906 59281 ...
## ..$ 2000 : num [1:209] 5341456 1282223 18560597 51151 59719 ...
## ..$ 2001 : num [1:209] 5564492 1315690 19198872 52341 61062 ...
## ..$ 2002 : num [1:209] 5795940 1352278 19854835 53583 63212 ...
## ..$ 2003 : num [1:209] 6036100 1391143 20529356 54864 65802 ...
## ..$ 2004 : num [1:209] 6285281 1430918 21222198 56166 68301 ...
## ..$ 2005 : num [1:209] 6543804 1470488 21932978 57474 70329 ...
## ..$ 2006 : num [1:209] 6812538 1512255 22625052 58679 71726 ...
## ..$ 2007 : num [1:209] 7091245 1553491 23335543 59894 72684 ...
## ..$ 2008 : num [1:209] 7380272 1594351 24061749 61118 73335 ...
## ..$ 2009 : num [1:209] 7679982 1635262 24799591 62357 73897 ...
## ..$ 2010 : num [1:209] 7990746 1676545 25545622 63616 74525 ...
## ..$ 2011 : num [1:209] 8316976 1716842 26216968 64817 75207 ...we will learn how to use both the excel_sheets() and the read_excel() function in combination with lapply() to read multiple sheets at once.
Reading a workbook
In the previous exercise you generated a list of three Excel sheets that you imported. However, loading in every sheet manually and then merging them in a list can be quite tedious. Luckily, you can automate this with lapply().
Have a look at the example code below:
my_workbook <- lapply(excel_sheets("data.xlsx"),
read_excel,
path = "data.xlsx")The read_excel() function is called multiple times on the "data.xlsx" file and each sheet is loaded in one after the other. The result is a list of data frames, each data frame representing one of the sheets in data.xlsx.
# Read all Excel sheets with lapply(): pop_list
pop_list <- lapply(excel_sheets("../xDatasets/urbanpop.xlsx"),
read_excel,
path = "../xDatasets/urbanpop.xlsx")
# Display the structure of pop_list
str(pop_list)## List of 3
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 8 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1960 : num [1:209] 769308 494443 3293999 NA NA ...
## ..$ 1961 : num [1:209] 814923 511803 3515148 13660 8724 ...
## ..$ 1962 : num [1:209] 858522 529439 3739963 14166 9700 ...
## ..$ 1963 : num [1:209] 903914 547377 3973289 14759 10748 ...
## ..$ 1964 : num [1:209] 951226 565572 4220987 15396 11866 ...
## ..$ 1965 : num [1:209] 1000582 583983 4488176 16045 13053 ...
## ..$ 1966 : num [1:209] 1058743 602512 4649105 16693 14217 ...
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 9 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1967 : num [1:209] 1119067 621180 4826104 17349 15440 ...
## ..$ 1968 : num [1:209] 1182159 639964 5017299 17996 16727 ...
## ..$ 1969 : num [1:209] 1248901 658853 5219332 18619 18088 ...
## ..$ 1970 : num [1:209] 1319849 677839 5429743 19206 19529 ...
## ..$ 1971 : num [1:209] 1409001 698932 5619042 19752 20929 ...
## ..$ 1972 : num [1:209] 1502402 720207 5815734 20263 22406 ...
## ..$ 1973 : num [1:209] 1598835 741681 6020647 20742 23937 ...
## ..$ 1974 : num [1:209] 1696445 763385 6235114 21194 25482 ...
## $ :Classes 'tbl_df', 'tbl' and 'data.frame': 209 obs. of 38 variables:
## ..$ country: chr [1:209] "Afghanistan" "Albania" "Algeria" "American Samoa" ...
## ..$ 1975 : num [1:209] 1793266 785350 6460138 21632 27019 ...
## ..$ 1976 : num [1:209] 1905033 807990 6774099 22047 28366 ...
## ..$ 1977 : num [1:209] 2021308 830959 7102902 22452 29677 ...
## ..$ 1978 : num [1:209] 2142248 854262 7447728 22899 31037 ...
## ..$ 1979 : num [1:209] 2268015 877898 7810073 23457 32572 ...
## ..$ 1980 : num [1:209] 2398775 901884 8190772 24177 34366 ...
## ..$ 1981 : num [1:209] 2493265 927224 8637724 25173 36356 ...
## ..$ 1982 : num [1:209] 2590846 952447 9105820 26342 38618 ...
## ..$ 1983 : num [1:209] 2691612 978476 9591900 27655 40983 ...
## ..$ 1984 : num [1:209] 2795656 1006613 10091289 29062 43207 ...
## ..$ 1985 : num [1:209] 2903078 1037541 10600112 30524 45119 ...
## ..$ 1986 : num [1:209] 3006983 1072365 11101757 32014 46254 ...
## ..$ 1987 : num [1:209] 3113957 1109954 11609104 33548 47019 ...
## ..$ 1988 : num [1:209] 3224082 1146633 12122941 35095 47669 ...
## ..$ 1989 : num [1:209] 3337444 1177286 12645263 36618 48577 ...
## ..$ 1990 : num [1:209] 3454129 1198293 13177079 38088 49982 ...
## ..$ 1991 : num [1:209] 3617842 1215445 13708813 39600 51972 ...
## ..$ 1992 : num [1:209] 3788685 1222544 14248297 41049 54469 ...
## ..$ 1993 : num [1:209] 3966956 1222812 14789176 42443 57079 ...
## ..$ 1994 : num [1:209] 4152960 1221364 15322651 43798 59243 ...
## ..$ 1995 : num [1:209] 4347018 1222234 15842442 45129 60598 ...
## ..$ 1996 : num [1:209] 4531285 1228760 16395553 46343 60927 ...
## ..$ 1997 : num [1:209] 4722603 1238090 16935451 47527 60462 ...
## ..$ 1998 : num [1:209] 4921227 1250366 17469200 48705 59685 ...
## ..$ 1999 : num [1:209] 5127421 1265195 18007937 49906 59281 ...
## ..$ 2000 : num [1:209] 5341456 1282223 18560597 51151 59719 ...
## ..$ 2001 : num [1:209] 5564492 1315690 19198872 52341 61062 ...
## ..$ 2002 : num [1:209] 5795940 1352278 19854835 53583 63212 ...
## ..$ 2003 : num [1:209] 6036100 1391143 20529356 54864 65802 ...
## ..$ 2004 : num [1:209] 6285281 1430918 21222198 56166 68301 ...
## ..$ 2005 : num [1:209] 6543804 1470488 21932978 57474 70329 ...
## ..$ 2006 : num [1:209] 6812538 1512255 22625052 58679 71726 ...
## ..$ 2007 : num [1:209] 7091245 1553491 23335543 59894 72684 ...
## ..$ 2008 : num [1:209] 7380272 1594351 24061749 61118 73335 ...
## ..$ 2009 : num [1:209] 7679982 1635262 24799591 62357 73897 ...
## ..$ 2010 : num [1:209] 7990746 1676545 25545622 63616 74525 ...
## ..$ 2011 : num [1:209] 8316976 1716842 26216968 64817 75207 ...The col_names argument
Apart from path and sheet, there are several other arguments you can specify in read_excel(). One of these arguments is called col_names.
By default it is TRUE, denoting whether the first row in the Excel sheets contains the column names. If this is not the case, you can set col_names to FALSE. In this case, R will choose column names for you. You can also choose to set col_names to a character vector with names for each column. It works exactly the same as in the readr package.
You’ll be working with the urbanpop_nonames.xlsx file. It contains the same data as urbanpop.xlsx but has no column names in the first row of the excel sheets.
# Import the first Excel sheet of urbanpop_nonames.xlsx (R gives names): pop_a
pop_a <- read_excel("../xDatasets/urbanpop_nonames.xlsx", col_names = FALSE)## New names:
## * `` -> `..1`
## * `` -> `..2`
## * `` -> `..3`
## * `` -> `..4`
## * `` -> `..5`
## * ... and 3 more# Import the first Excel sheet of urbanpop_nonames.xlsx (specify col_names): pop_b
cols <- c("country", paste0("year_", 1960:1966))
pop_b <- read_excel("../xDatasets/urbanpop_nonames.xlsx", col_names = cols)
# Print the summary of pop_a
sum_pop_a <- as.data.frame(do.call(cbind, lapply(pop_a, summary)))## Warning in (function (..., deparse.level = 1) : number of rows of result is
## not a multiple of vector length (arg 1)sum_pop_a %>%
kable() %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = T, position = "left", , font_size = 11) %>%
row_spec(0, bold = T, color = "white", background = "#3f7689")| ..1 | ..2 | ..3 | ..4 | ..5 | ..6 | ..7 | ..8 | |
|---|---|---|---|---|---|---|---|---|
| Min. | 209 | 3378 | 1028.344532 | 1089.933922 | 1153.603196 | 1217.930058 | 1281.49467 | 1348.89911 |
| 1st Qu. | character | 88977.5 | 70644.108696 | 74973.524208 | 81869.73424 | 84952.961648 | 88633.26703 | 93638.353542 |
| Median | character | 580675 | 570158.53128 | 593967.538248 | 619331.07068 | 645261.590826 | 679109.1204 | 735139.3668 |
| Mean | 209 | 4988124.33333333 | 4991613.4620824 | 5141591.79013524 | 5303711.07780988 | 5468965.88001294 | 5637394.23704651 | 5790281.36374468 |
| 3rd Qu. | character | 3077228.5 | 2807280.200284 | 2948396.094116 | 3148940.981158 | 3296444.49587 | 3317422.0327 | 3418036.001856 |
| Max. | character | 126469700 | 129268132.666 | 131974142.696 | 134599886.436 | 137205240.336 | 139663053.37 | 141962708.16 |
| NA’s | 209 | 11 | 1028.344532 | 1089.933922 | 1153.603196 | 1217.930058 | 1281.49467 | 1348.89911 |
# Print the summary of pop_b
sum_pop_b <- as.data.frame(do.call(cbind, lapply(pop_b, summary))) ## Warning in (function (..., deparse.level = 1) : number of rows of result is
## not a multiple of vector length (arg 1)sum_pop_b %>%
kable() %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = T, position = "left", , font_size = 11) %>%
row_spec(0, bold = T, color = "white", background = "#3f7689")| country | year_1960 | year_1961 | year_1962 | year_1963 | year_1964 | year_1965 | year_1966 | |
|---|---|---|---|---|---|---|---|---|
| Min. | 209 | 3378 | 1028.344532 | 1089.933922 | 1153.603196 | 1217.930058 | 1281.49467 | 1348.89911 |
| 1st Qu. | character | 88977.5 | 70644.108696 | 74973.524208 | 81869.73424 | 84952.961648 | 88633.26703 | 93638.353542 |
| Median | character | 580675 | 570158.53128 | 593967.538248 | 619331.07068 | 645261.590826 | 679109.1204 | 735139.3668 |
| Mean | 209 | 4988124.33333333 | 4991613.4620824 | 5141591.79013524 | 5303711.07780988 | 5468965.88001294 | 5637394.23704651 | 5790281.36374468 |
| 3rd Qu. | character | 3077228.5 | 2807280.200284 | 2948396.094116 | 3148940.981158 | 3296444.49587 | 3317422.0327 | 3418036.001856 |
| Max. | character | 126469700 | 129268132.666 | 131974142.696 | 134599886.436 | 137205240.336 | 139663053.37 | 141962708.16 |
| NA’s | 209 | 11 | 1028.344532 | 1089.933922 | 1153.603196 | 1217.930058 | 1281.49467 | 1348.89911 |
Did you spot the difference between the summaries? It’s really crucial to correctly tell R whether your Excel data contains column names. If you don’t, the head of the data frame you end up with will contain incorrect information.
The skip argument
Another argument that can be very useful when reading in Excel files that are less tidy, is skip. With skip, you can tell R to ignore a specified number of rows inside the Excel sheets you’re trying to pull data from. Have a look at this example:
read_excel("data.xlsx", skip = 15)
In this case, the first 15 rows in the first sheet of "data.xlsx" are ignored.
If the first row of this sheet contained the column names, this information will also be ignored by readxl. Make sure to set col_names to FALSE or manually specify column names in this case!
# Import the second sheet of urbanpop.xlsx, skipping the first 21 rows: urbanpop_sel
urbanpop_sel <- read_excel("../xDatasets/urbanpop.xlsx", sheet = 2, col_names = FALSE, skip = 21)## New names:
## * `` -> `..1`
## * `` -> `..2`
## * `` -> `..3`
## * `` -> `..4`
## * `` -> `..5`
## * ... and 4 more# Print out the first observation from urbanpop_sel
urbanpop_sel[1,]## # A tibble: 1 x 9
## ..1 ..2 ..3 ..4 ..5 ..6 ..7 ..8 ..9
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Benin 382022. 411859. 443013. 475611. 515820. 557938. 602093. 648410.Time to learn about another package to import data from Excel: gdata.
gdata package
Import a local file
In this part of the chapter you’ll learn how to import .xls files using the gdata package. Similar to the readxl package, you can import single Excel sheets from Excel sheets to start your analysis in R.
You’ll be working with the urbanpop.xls dataset, the .xls version of the Excel file you’ve been working with before.
# Load the gdata package
library(gdata)
prl <- "C:/myperl/perl/bin/perl5.28.1.exe"
# Import the second sheet of urbanpop.xls: urban_pop
urban_pop <- read.xls("../xDatasets/urbanpop.xls", perl = prl)
# Print the first 11 observations using head()
urban_pop %>%
head(11) %>%
kable() %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = T, position = "left", , font_size = 11) %>%
row_spec(0, bold = T, color = "white", background = "#3f7689")| country | X1960 | X1961 | X1962 | X1963 | X1964 | X1965 | X1966 |
|---|---|---|---|---|---|---|---|
| Afghanistan | 769308 | 814923.049 | 858521.698 | 903913.86 | 951225.94 | 1000582.35 | 1058743.47 |
| Albania | 494443 | 511802.780 | 529438.851 | 547376.75 | 565571.75 | 583982.89 | 602512.17 |
| Algeria | 3293999 | 3515147.548 | 3739963.007 | 3973289.13 | 4220987.01 | 4488175.64 | 4649105.24 |
| American Samoa | NA | 13660.298 | 14165.797 | 14758.93 | 15396.42 | 16044.82 | 16693.11 |
| Andorra | NA | 8723.921 | 9700.346 | 10748.38 | 11865.86 | 13052.75 | 14216.81 |
| Angola | 521205 | 548265.046 | 579695.370 | 612086.70 | 645261.59 | 679109.12 | 717833.40 |
| Antigua and Barbuda | 21699 | 21635.051 | 21664.200 | 21740.74 | 21830.18 | 21908.89 | 22003.13 |
| Argentina | 15224096 | 15545222.586 | 15912120.018 | 16282345.35 | 16654412.49 | 17027711.84 | 17389812.09 |
| Armenia | 957974 | 1008597.321 | 1061426.399 | 1115612.32 | 1170683.41 | 1226270.42 | 1281581.61 |
| Aruba | 24996 | 28139.757 | 28532.729 | 28763.12 | 28923.39 | 29082.53 | 29252.23 |
| Australia | 8375329 | 8587694.566 | 8841890.588 | 9055934.70 | 9279084.65 | 9507271.80 | 9768314.91 |
There seems to be a lot of missing data, but read.xls() knows how to handle it.
read.xls() wraps around read.table()
Remember how read.xls() actually works? It basically comes down to two steps: converting the Excel file to a .csv file using a Perl script, and then reading that .csv file with the read.csv() function that is loaded by default in R, through the utils package.
This means that all the options that you can specify in read.csv(), can also be specified in read.xls().
# Column names for urban_pop
columns <- c("country", paste0("year_", 1967:1974))
# Finish the read.xls call
urban_pop <- read.xls("../xDatasets/urbanpop.xls", sheet = 2,
skip = 50, header = FALSE, stringsAsFactors = FALSE,
col.names = columns,
perl = prl)
# Print first 10 observation of urban_pop
urban_pop[1:10, ] %>%
kable() %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = T, position = "left", , font_size = 11) %>%
row_spec(0, bold = T, color = "white", background = "#3f7689")| country | year_1967 | year_1968 | year_1969 | year_1970 | year_1971 | year_1972 | year_1973 | year_1974 |
|---|---|---|---|---|---|---|---|---|
| Cyprus | 231929.74 | 237831.38 | 243983.34 | 250164.52 | 261213.21 | 272407.99 | 283774.90 | 295379.83 |
| Czech Republic | 6204409.91 | 6266304.50 | 6326368.97 | 6348794.89 | 6437055.17 | 6572632.32 | 6718465.53 | 6873458.18 |
| Denmark | 3777552.62 | 3826785.08 | 3874313.99 | 3930042.97 | 3981360.12 | 4028247.92 | 4076867.28 | 4120201.43 |
| Djibouti | 77788.04 | 84694.35 | 92045.77 | 99845.22 | 107799.69 | 116098.23 | 125391.58 | 136606.25 |
| Dominica | 27550.36 | 29527.32 | 31475.62 | 33328.25 | 34761.52 | 36049.99 | 37260.05 | 38501.47 |
| Dominican Republic | 1535485.43 | 1625455.76 | 1718315.40 | 1814060.00 | 1915590.38 | 2020157.01 | 2127714.45 | 2238203.87 |
| Ecuador | 2059355.12 | 2151395.14 | 2246890.79 | 2345864.41 | 2453817.78 | 2565644.81 | 2681525.25 | 2801692.62 |
| Egypt | 13798171.00 | 14248342.19 | 14703858.22 | 15162858.52 | 15603661.36 | 16047814.69 | 16498633.27 | 16960827.93 |
| El Salvador | 1345528.98 | 1387218.33 | 1429378.98 | 1472181.26 | 1527985.34 | 1584758.18 | 1642098.95 | 1699470.87 |
| Equatorial Guinea | 75364.50 | 77295.03 | 78445.74 | 78411.07 | 77055.29 | 74596.06 | 71438.96 | 68179.26 |
Work that Excel data!
Now that you can read in Excel data, let’s try to clean and merge it. You already used the cbind() function some exercises ago. Let’s take it one step further now.
Make sure the first column of urban_sheet2 and urban_sheet3 are removed, so you don’t have duplicate columns.
# Add code to import data from all three sheets in urbanpop.xls
path <- "../xDatasets/urbanpop.xls"
urban_sheet1 <- read.xls(path, sheet = 1, stringsAsFactors = FALSE, perl = prl)
urban_sheet2 <- read.xls(path, sheet = 2, stringsAsFactors = FALSE, perl = prl)
urban_sheet3 <- read.xls(path, sheet = 3, stringsAsFactors = FALSE, perl = prl)
# Extend the cbind() call to include urban_sheet3: urban
urban <- cbind(urban_sheet1, urban_sheet2[-1], urban_sheet3[-1])
# Remove all rows with NAs from urban: urban_clean
urban_clean <- na.omit(urban)
# Print out a summary of urban_clean
sum_urban_clean <- as.data.frame(do.call(cbind, lapply(urban_clean, summary)))
sum_urban_clean %>%
kable() %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = T, position = "left", , font_size = 11) %>%
row_spec(0, bold = T, color = "white", background = "#3f7689")| country | X1960 | X1961 | X1962 | X1963 | X1964 | X1965 | X1966 | X1967 | X1968 | X1969 | X1970 | X1971 | X1972 | X1973 | X1974 | X1975 | X1976 | X1977 | X1978 | X1979 | X1980 | X1981 | X1982 | X1983 | X1984 | X1985 | X1986 | X1987 | X1988 | X1989 | X1990 | X1991 | X1992 | X1993 | X1994 | X1995 | X1996 | X1997 | X1998 | X1999 | X2000 | X2001 | X2002 | X2003 | X2004 | X2005 | X2006 | X2007 | X2008 | X2009 | X2010 | X2011 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Min. | 197 | 3378 | 3432.538728 | 3481.185172 | 3531.63859 | 3585.76687 | 3644.38707 | 3706.277064 | 3771.200916 | 3835.22167 | 3893.072722 | 3941.19174 | 4016.945328 | 4084.374744 | 4146.086628 | 4206.141156 | 4267.29312 | 4334.0081 | 4401.74592 | 4470.32184 | 4539.003 | 4607.42362 | 4644.608 | 4681.3188 | 4716.47137 | 4750.0775 | 4781.7966 | 4809.142888 | 4834.770028 | 4859.038692 | 4882.810482 | 4906.75008 | 4945.55556 | 4984.68096 | 5023.777016 | 5062.335888 | 5099.69562 | 5078.770048 | 5054.95452 | 5028.75972 | 5000.50988 | 4970.6589 | 5002.535978 | 5033.946704 | 5063.697436 | 5089.73142 | 5111.06776 | 5135.30628 | 5155.113348 | 5172.283852 | 5188.585512 | 5205.90336 | 5232.931168 |
| 1st Qu. | character | 87735 | 92904.606756 | 98330.948736 | 104988.264446 | 112084.457352 | 119321.97 | 128565.463 | 138024.19245 | 147845.8773 | 158252.45345 | 171063.02556 | 181482.89978 | 189492.09236 | 197792.41178 | 205410.218984 | 211745.77114 | 216991.27989 | 222209.073796 | 227604.825726 | 233461.342926 | 242583.3337 | 248948.025406 | 257944.331886 | 274139.40162 | 284939.32716 | 300927.86451 | 307698.837706 | 321125.123018 | 334616.166066 | 347348.192094 | 370151.77112 | 394611.435736 | 418787.854768 | 427457.223172 | 435958.850304 | 461992.88564 | 488136.271732 | 494203.061056 | 498001.634676 | 505143.58818 | 525628.65362 | 550637.602854 | 567530.80956 | 572094.270328 | 593900.32896 | 620510.59287 | 632658.612652 | 645171.676586 | 658016.598168 | 671085.46426 | 684302.43284 | 698008.7312 |
| Median | character | 599714 | 630787.860064 | 659463.754092 | 704989.332 | 740609.01508 | 774957.04434 | 809767.5527 | 838449.4164 | 890269.65478 | 929449.58876 | 976470.6 | 1008630.17884 | 1048737.86094 | 1097292.7 | 1159401.941992 | 1223145.5664 | 1249829.494308 | 1311276.14376 | 1340810.8726 | 1448185.064196 | 1592396.63611 | 1673078.53086 | 1713060.2548 | 1730625.51714 | 1749032.62549 | 1786125.21997 | 1850910.32 | 1953693.62 | 1997010.506664 | 1993543.888182 | 2066505.1884 | 2150229.608434 | 2237404.79102 | 2322157.565002 | 2410297.27952 | 2482392.9936 | 2522459.905056 | 2606124.829448 | 2664983.044388 | 2737808.865544 | 2826647.08158 | 2925851.195664 | 2928251.802 | 2944934.454 | 2994356.123856 | 3057922.97631 | 3269963.03326 | 3432024.00765 | 3589394.7688 | 3652338.21372 | 3676309.32994 | 3664663.54878 |
| Mean | 197 | 5012387.87309645 | 5282487.61712411 | 5440971.99436048 | 5612311.82411641 | 5786960.83325094 | 5964969.50655066 | 6126413.05052034 | 6288770.71247333 | 6451366.61260926 | 6624909.36219931 | 6799109.86466731 | 6980894.9171615 | 7165337.95755494 | 7349454.48592461 | 7540446.08390846 | 7731972.67019076 | 7936401.03589518 | 8145944.80521005 | 8361360.04357668 | 8583137.52307083 | 8808772.39480757 | 9049162.89768697 | 9295226.04183367 | 9545035.25760904 | 9798558.74241767 | 10058661.1780371 | 10323838.5270233 | 10595816.5773639 | 10873040.5126198 | 11154457.6425615 | 11438542.7407744 | 11725075.8943269 | 12010922.414318 | 12296948.8773616 | 12582930.152931 | 12871479.9626007 | 13165923.5120549 | 13463675.1956097 | 13762860.8232182 | 14063387.4536247 | 14369278.3499752 | 14705743.0676065 | 15043381.4434264 | 15384512.7213773 | 15730299.352009 | 16080261.7122772 | 16435871.5953336 | 16797483.6272271 | 17164897.8227208 | 17533996.7915159 | 17904811.0932721 | 18276297.2851597 |
| 3rd Qu. | character | 3130085 | 3155370.253552 | 3250211.15652 | 3416489.86485 | 3585464.2416 | 3666723.56576 | 3871757.260564 | 4019905.62298 | 4158186.16246 | 4300668.64452 | 4440047.202 | 4595966.14701 | 4766544.542256 | 4838296.681788 | 4906384.412208 | 5003370.43087 | 5121117.800904 | 5227677.348276 | 5352746.487088 | 5558849.899624 | 5815772.45784 | 6070456.957982 | 6337995.102416 | 6619986.67603 | 6918260.596384 | 6931779.72735 | 6935762.653904 | 6939904.820202 | 6945022.351696 | 6885378.320786 | 6830026.21944 | 6816589.3548 | 6820099.44 | 7139656.049106 | 7499901.168492 | 7708571.45679 | 7686092.37392 | 7664315.67911 | 7784055.519752 | 8083487.62116 | 8305563.75698 | 8421967.008 | 8448628.104 | 8622731.607028 | 8999112.337684 | 9394001.32608 | 9689807.3712 | 9803380.896 | 10210317.370344 | 10518288.968376 | 10618596.2296 | 10731192.832 |
| Max. | character | 126469700 | 129268132.666 | 131974142.696 | 134599886.436 | 137205240.336 | 139663053.37 | 141962708.16 | 144201721.584 | 146340364.368 | 148475900.598 | 150922373.04 | 152863830.642 | 154530472.704 | 156034106.334 | 157488073.512 | 159452730 | 165583752.46 | 171550309.56 | 177605736.42 | 183785364.32 | 189947471.3 | 199385257.62 | 209435967.72 | 219680097.56 | 229872397.1 | 240414889.6 | 251630158.04 | 263433513.42 | 275570541.24 | 287810746.6 | 300165617.7 | 314689997.24 | 329099365.12 | 343555326.96 | 358232229.62 | 373035156.55 | 388936607.1 | 405031715.55 | 421147610.11 | 437126845.43 | 452999146.65 | 473204511 | 493402140 | 513607776 | 533892174.75 | 554367818.4 | 575050080.56 | 595731463.86 | 616552721.82 | 637533975.76 | 658557734.5 | 678796403.04 |
Time for something totally different: XLConnect